const { promisePool, Pool } = require('../models/db')

const treeModel = require('../models/treeModel')

const JWT = require('../utils/JWT')

// 导入 bcryptjs 加密包
const bcrypt = require('bcryptjs')

//导入 moment
const moment = require('moment');

const donateRecordService = {
  // 列表
  // list2: (req, res) => {
  //   // return
  //   const to = ((req.body.page) * req.body.pageSize)
  //   const from = ((req.body.page - 1) * req.body.pageSize) + 1 
  //   const list = `SELECT planting_tree.*, COUNT(planting_tree_specification.planting_tree_id) AS planting_tree_specification_count FROM planting_tree LEFT JOIN planting_tree_specification ON planting_tree.id = planting_tree_specification.planting_tree_id GROUP BY planting_tree.id ORDER BY planting_tree.sort DESC, planting_tree.created_at DESC LIMIT ${to} OFFSET ${from}`
  //   Pool.query(list, function (err, rows) {
  //     console.log(err, 'err');
  //     if (err) {
  //       return res.codeMsg(err)
  //     }
  //     console.log(rows, 'rows');
  //     const data = rows.map(item => {
  //       const created_at = moment(item.created_at, 'D/M/YYYY HH:mm:ss', true)
  //       const updated_at = moment(item.updated_at, 'D/M/YYYY HH:mm:ss', true)
  //       return {
  //         ...item,
  //         created_at: moment(created_at).format('YYYY-MM-DD HH:mm:ss'),
  //         updated_at: moment(updated_at).format('YYYY-MM-DD HH:mm:ss'),
  //       }
  //     })
  //     // 2、发送token到客户端（header方式）
  //     // res.header('Authorization', token)
  //     // const data2 = data.filter((item, index) => index < 10)
  //     return res.send({
  //       code: 0,
  //       msg: '成功',
  //       data: {
  //         data,
  //         from: ((req.body.page - 1) * req.body.pageSize) + 1,
  //         to: ((req.body.page) * req.body.pageSize),
  //         per_page: Math.ceil(data.length /  req.body.pageSize),
  //         total: data.length
  //       }
  //     })

  //     // 登录成功
  //     // res.codeMsg('登录成功', 0)
  //   })
  // },
  list : (req, res) => {
  const to = ((req.body.page) * req.body.pageSize);
  const from = ((req.body.page - 1) * req.body.pageSize) + 1;
  const countQuery = `SELECT COUNT(*) AS total FROM planting_tree`;
  const listQuery = `SELECT planting_tree.*, COUNT(planting_tree_specification.planting_tree_id) AS planting_tree_specification_count 
                    FROM planting_tree 
                    LEFT JOIN planting_tree_specification 
                    ON planting_tree.id = planting_tree_specification.planting_tree_id 
                    GROUP BY planting_tree.id 
                    ORDER BY planting_tree.sort DESC, planting_tree.created_at DESC 
                    LIMIT ${to} OFFSET ${from}`;
  
  Pool.query(countQuery, function (err, countResult) {
    if (err) {
      console.log(err);
      return res.codeMsg(err);
    }
    
    const total = countResult[0].total;
    
    Pool.query(listQuery, function (err, rows) {
      if (err) {
        console.log(err);
        return res.codeMsg(err);
      }
      
      const data = rows.map(item => {
        const created_at = moment(item.created_at, 'D/M/YYYY HH:mm:ss', true);
        const updated_at = moment(item.updated_at, 'D/M/YYYY HH:mm:ss', true);
        return {
          ...item,
          created_at: moment(created_at).format('YYYY-MM-DD HH:mm:ss'),
          updated_at: moment(updated_at).format('YYYY-MM-DD HH:mm:ss'),
        };
      });
      
      return res.send({
        code: 0,
        msg: '成功',
        data: {
          data,
          from: from,
          to: to,
          per_page: Math.ceil(data.length / req.body.pageSize),
          total: total
        }
      });
    });
  });
  },
  paramsList: (req, res) => {
    // return
    let paramsList = 'select * from planting_tree';
    const params = req.body.search_key
    const to = ((req.body.page) * req.body.pageSize)
    const from = ((req.body.page - 1) * req.body.pageSize) + 1 
    // 判断 params 中是否存在 search_key，并且该对象非空
    if (params && Object.keys(params).length > 0) {
      // 构建 WHERE 子句的条件数组
      const conditions = [];

      // 遍历 search_key 对象的每个属性
      for (const key in params) {
        // 获取属性值
        const value = params[key];
        let formattedValue;

        if (key.endsWith('_at') && Array.isArray(value) && value.length === 2) {
          // 如果是 donate_at 属性，并且值是一个包含两个日期对象的数组，则构建时间范围查询条件
          // 将日期字符串转换为 Date 对象
          const startISO = value[0].slice(0, 19);
          const endISO = value[1].slice(0, 19);
          formattedValue = ` BETWEEN '${startISO}' AND '${endISO}'`;
        } else {
          // 否则，使用默认的格式化方法
          // formattedValue = typeof value === 'string' ? ' = ' + `'${value}'` : ' = ' + value;
          formattedValue = typeof value === 'string' ? ` LIKE '%${value}%'` : ` LIKE '%${value}%'`;
        }
        // 添加条件到数组中
        conditions.push(`${key}${formattedValue}`);
      }

      // console.log(conditions, 'conditions');

      // 构建 WHERE 子句
      const whereClause = conditions.length > 0 ? ` where ${conditions.join(' and ')}` : '';
      // console.log(whereClause, 'whereClause');
      // 将 WHERE 子句添加到查询语句中
      paramsList = `${paramsList}${whereClause}`;
      Pool.query(paramsList, function (err, rows) {
        if (err) {
          return res.codeMsg(err)
        }
        // console.log(rows, 'rows');

        const data = rows.map(item => {
          const created_at = moment(item.created_at, 'D/M/YYYY HH:mm:ss', true)
          const updated_at = moment(item.updated_at, 'D/M/YYYY HH:mm:ss', true)
          return {
            ...item,
            created_at: moment(created_at).format('YYYY-MM-DD HH:mm:ss'),
            updated_at: moment(updated_at).format('YYYY-MM-DD HH:mm:ss'),
          }
        })
        // 2、发送token到客户端（header方式）
        return res.send({
          code: 0,
          msg: '成功',
          data: {
            data,
            from: ((req.body.page - 1) * req.body.pageSize) + 1,
            to: ((req.body.page) * req.body.pageSize),
            per_page: Math.ceil(data.length /  req.body.pageSize),
            total: data.length
          }
        })
      })
    }


  },
  // 插入设置
  add: (req, res) => {
    const params = {
      ...req.body,
      //修改 time 属性的值
      created_at: moment().format('YYYY-MM-DD HH:mm:ss'),
      updated_at: moment().format('YYYY-MM-DD HH:mm:ss'),
    }
    Pool.query(
      treeModel.add,
      // [{ username: username, password: password }],
      [params],
      function (err, rows) {
        console.log(err, 'err');
        console.log(params, 'params');
        if (err) {
          return res.codeMsg(err)
        }

        if (rows.affectedRows !== 1) {
          return res.codeMsg('注册用户失败，请稍后再试！')
        }

        // 注册成功提示
        // res.send({ code: 0, msg: '注册成功！' })
        return res.codeMsg('注册成功！', 0)
      }
    )
  },
  // 更新设置
  update: (req, res) => {
    const params = { ...req.body, updated_at: moment().format('YYYY-MM-DD HH:mm:ss') }
    console.log(params, 'params');
    Pool.query(
      treeModel.update,
      [params, req.body.id],
      function (err, rows) {
        console.log(err, 'err');
        if (err) return res.codeMsg(err)

        if (rows.affectedRows !== 1) return res.codeMsg('更新失败！')
        console.log(111);
        return res.codeMsg('更新成功！', 0)
      }
    )
  },
  // 删除设置
  delete: (req, res) => {
    Pool.query(treeModel.delete, [req.body.id], function (err, rows) {
      console.log(err, 'err');
      if (err) return res.codeMsg(err)
      console.log(1);
      if (rows.affectedRows !== 1) return res.codeMsg('您要删除的内存不存在！')
      console.log(2);
      return res.codeMsg('删除成功！', 0)
    })
  },
}

module.exports = donateRecordService
